package DB;

import POJO.CommentObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Sibrand
 */
public class CommentManager extends DB {

    public CommentManager(String dbName, String userName, String password) {
        super(dbName, userName, password);
    }

    public CommentManager(Connection c) {
        conn = c;
    }

    public boolean isWrittenBy(String user, int commentID) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Comments WHERE postedBy= ? AND commentID= ?");
                pstmt.setString(1, user);
                pstmt.setInt(2, commentID);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return true;
                }
                return false;
            } catch (SQLException ex) {
                return false;
            }
        } else {
            return false;
        }
    }

    public int getNbCommentsOnContent(int contentID) {
        int result = 0;
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT commentID FROM Comments WHERE replyOnContentID= ? ORDER BY postedOn DESC");
                pstmt.setInt(1, contentID);
                ResultSet rs = pstmt.executeQuery();

                while (rs.next()) {
                    ++result;
                }
                return result;
            } catch (SQLException ex) {
                return result;
            }
        } else {
            return result;
        }
    }

    public int getNbCommentsOnComment(int contentID) {
        int result = 0;
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT commentID FROM Comments WHERE replyOnCommentID= ? ORDER BY postedOn DESC");
                pstmt.setInt(1, contentID);
                ResultSet rs = pstmt.executeQuery();

                while (rs.next()) {
                    ++result;
                }
                return result;
            } catch (SQLException ex) {
                return result;
            }
        } else {
            return result;
        }
    }

    public int[] getCommentsOnContent(int contentID) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT commentID FROM Comments WHERE replyOnContentID= ? ORDER BY postedOn DESC");
                pstmt.setInt(1, contentID);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public int[] getCommentsOnComment(int commentID) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT commentID FROM Comments WHERE replyOnCommentID= ? ORDER BY postedOn DESC");
                pstmt.setInt(1, commentID);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public CommentObject getComment(int commentID) {
        CommentObject result = new CommentObject();
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Comments WHERE commentID= ?");
                pstmt.setInt(1, commentID);
                ResultSet rs = pstmt.executeQuery();


                if (rs.next()) {
                    result.commentID = rs.getInt(1);
                    result.text = rs.getString(2);
                    result.author = rs.getString(3);
                    result.postedOn = rs.getString(4);
                    result.contentID = rs.getInt(5);
                    result.onCommentID = rs.getInt(6);
                    result.viewedByAddressee = rs.getInt(7);
                }
                return result;
            } catch (SQLException ex) {
                return result;
            }
        } else {
            return result;
        }
    }

    public void changeComment(int commentID, String text) {
        if (isConnected()) {
            try {

                PreparedStatement pstmt = conn.prepareStatement("UPDATE Comments SET text=? WHERE commentID= ?");
                pstmt.setInt(2, commentID);
                pstmt.setString(1, text);
                pstmt.execute();
            } catch (SQLException ex) {
            }
        }
    }

    public void removeComment(int commentID) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM Comments WHERE commentID= ?");
                pstmt.setInt(1, commentID);
                pstmt.execute();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean addCommentToContent(String writer, String text, int content) {
        ContentContainerManager t = new ContentContainerManager(conn);
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Comments (text, postedBy, replyOnContentID) VALUES (?, ?, ?)");
                pstmt.setString(1, text);
                pstmt.setString(2, writer);
                pstmt.setInt(3, content);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }
        }
        return false;
    }

    public boolean addCommentToComment(String writer, String text, int comment) {
        ContentContainerManager t = new ContentContainerManager(conn);
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Comments (text, postedBy, replyOnCommentID) VALUES (?, ?, ?)");
                pstmt.setString(1, text);
                pstmt.setString(2, writer);
                pstmt.setInt(3, comment);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }
        }
        return false;
    }

    public static String escapeHTML(String s) {
        StringBuffer sb = new StringBuffer();
        int n = s.length();
        for (int i = 0; i < n; i++) {
            char c = s.charAt(i);
            switch (c) {
                case '<':
                    sb.append("&lt;");
                    break;
                case '>':
                    sb.append("&gt;");
                    break;
                case '&':
                    sb.append("&amp;");
                    break;
                case '"':
                    sb.append("&quot;");
                    break;
                case 'à':
                    sb.append("&agrave;");
                    break;
                case 'À':
                    sb.append("&Agrave;");
                    break;
                case 'â':
                    sb.append("&acirc;");
                    break;
                case 'Â':
                    sb.append("&Acirc;");
                    break;
                case 'ä':
                    sb.append("&auml;");
                    break;
                case 'Ä':
                    sb.append("&Auml;");
                    break;
                case 'å':
                    sb.append("&aring;");
                    break;
                case 'Å':
                    sb.append("&Aring;");
                    break;
                case 'æ':
                    sb.append("&aelig;");
                    break;
                case 'Æ':
                    sb.append("&AElig;");
                    break;
                case 'ç':
                    sb.append("&ccedil;");
                    break;
                case 'Ç':
                    sb.append("&Ccedil;");
                    break;
                case 'é':
                    sb.append("&eacute;");
                    break;
                case 'É':
                    sb.append("&Eacute;");
                    break;
                case 'è':
                    sb.append("&egrave;");
                    break;
                case 'È':
                    sb.append("&Egrave;");
                    break;
                case 'ê':
                    sb.append("&ecirc;");
                    break;
                case 'Ê':
                    sb.append("&Ecirc;");
                    break;
                case 'ë':
                    sb.append("&euml;");
                    break;
                case 'Ë':
                    sb.append("&Euml;");
                    break;
                case 'ï':
                    sb.append("&iuml;");
                    break;
                case 'Ï':
                    sb.append("&Iuml;");
                    break;
                case 'ô':
                    sb.append("&ocirc;");
                    break;
                case 'Ô':
                    sb.append("&Ocirc;");
                    break;
                case 'ö':
                    sb.append("&ouml;");
                    break;
                case 'Ö':
                    sb.append("&Ouml;");
                    break;
                case 'ø':
                    sb.append("&oslash;");
                    break;
                case 'Ø':
                    sb.append("&Oslash;");
                    break;
                case 'ß':
                    sb.append("&szlig;");
                    break;
                case 'ù':
                    sb.append("&ugrave;");
                    break;
                case 'Ù':
                    sb.append("&Ugrave;");
                    break;
                case 'û':
                    sb.append("&ucirc;");
                    break;
                case 'Û':
                    sb.append("&Ucirc;");
                    break;
                case 'ü':
                    sb.append("&uuml;");
                    break;
                case 'Ü':
                    sb.append("&Uuml;");
                    break;
                case '®':
                    sb.append("&reg;");
                    break;
                case '©':
                    sb.append("&copy;");
                    break;
                case '€':
                    sb.append("&euro;");
                    break;
                default:
                    sb.append(c);
                    break;
            }
        }
        return sb.toString();
    }
}
